From a049e48dd45ac3ead94887dbbd40ac887b61b9ff Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc kop@karlpinc.com" <kop@karlpinc.com>
Date: Tue, 4 Aug 2026 17:20:34 +0000
Subject: [PATCH] Fix so some special SIGHTING_RECORDS rows need not exist

---
 .../functions/create/build_swelling_states.m4   | 17 +++++++++++++++++
 doc/src/functions/build_swelling_states.m4      |  6 ++++++
 2 files changed, 23 insertions(+)

diff --git a/db/schemas/sokwedb/functions/create/build_swelling_states.m4 b/db/schemas/sokwedb/functions/create/build_swelling_states.m4
index 10a2765..314bfaf 100644
--- a/db/schemas/sokwedb/functions/create/build_swelling_states.m4
+++ b/db/schemas/sokwedb/functions/create/build_swelling_states.m4
@@ -97,6 +97,11 @@ CREATE OR REPLACE FUNCTION build_swelling_states(a_animid TEXT)
   --
   -- Remarks:
   --   Likely not as efficient as it might be.  We don't care.
+  --
+  --   All the cross-product joining of SIGHTING_RECORDS is just
+  -- in case the speical values don't exist that correspond with
+  -- each table searched.  When the SIGHTING_RECORDS row does
+  -- not exist, the corresponding table is not searched.
 
   DECLARE
     a_result INT := 0;
@@ -123,24 +128,30 @@ CREATE OR REPLACE FUNCTION build_swelling_states(a_animid TEXT)
           JOIN arrivals_a ON (arrivals_a.eid = obs.eid)
           JOIN roles ON (roles.eid = obs.eid)
           JOIN cycle_states ON (cycle_states.code = arrivals_a.swelling)
+           , sighting_records
         WHERE roles.participant = a_animid
               AND cycle_states.ssrank IS NOT NULL
+              AND sighting_records.code = 'sdb_arrivals_a_source'
       UNION
       SELECT obs.date
         FROM obs
           JOIN arrivals ON (arrivals.eid = obs.eid)
           JOIN roles ON (roles.eid = obs.eid)
           JOIN cycle_states ON (cycle_states.code = arrivals.cycle)
+           , sighting_records
         WHERE roles.participant = a_animid
               AND cycle_states.ssrank IS NOT NULL
+              AND sighting_records.code = 'sdb_arrivals_source'
       UNION
       SELECT obs.date
         FROM obs
           JOIN matings ON (matings.eid = obs.eid)
           JOIN roles ON (roles.eid = obs.eid)
           JOIN cycle_states ON (cycle_states.code = matings.swelling)
+           , sighting_records
         WHERE roles.participant = a_animid
               AND cycle_states.ssrank IS NOT NULL
+              AND sighting_records.code = 'sdb_matings_source'
       UNION
       SELECT swelling_sources.date
         FROM swelling_sources
@@ -157,9 +168,11 @@ CREATE OR REPLACE FUNCTION build_swelling_states(a_animid TEXT)
              JOIN arrivals_a ON (arrivals_a.eid = obs.eid)
              JOIN roles ON (roles.eid = obs.eid)
              JOIN cycle_states ON (cycle_states.code = arrivals_a.swelling)
+              , sighting_records
            WHERE roles.participant = a_animid
                  AND obs.date = a_date
                  AND cycle_states.ssrank IS NOT NULL
+                 AND sighting_records.code = 'sdb_arrivals_a_source'
          UNION
          SELECT arrivals.cycle, arrivals.cycle
               , 'sdb_arrivals_source'
@@ -167,9 +180,11 @@ CREATE OR REPLACE FUNCTION build_swelling_states(a_animid TEXT)
              JOIN arrivals ON (arrivals.eid = obs.eid)
              JOIN roles ON (roles.eid = obs.eid)
              JOIN cycle_states ON (cycle_states.code = arrivals.cycle)
+              , sighting_records
            WHERE roles.participant = a_animid
                  AND obs.date = a_date
                  AND cycle_states.ssrank IS NOT NULL
+                 AND sighting_records.code = 'sdb_arrivals_source'
          UNION
          SELECT matings.swelling, matings.swelling
               , 'sdb_matings_source'
@@ -177,9 +192,11 @@ CREATE OR REPLACE FUNCTION build_swelling_states(a_animid TEXT)
              JOIN matings ON (matings.eid = obs.eid)
              JOIN roles ON (roles.eid = obs.eid)
              JOIN cycle_states ON (cycle_states.code = matings.swelling)
+              , sighting_records
            WHERE roles.participant = a_animid
                  AND obs.date = a_date
                  AND cycle_states.ssrank IS NOT NULL
+                 AND sighting_records.code = 'sdb_matings_source'
          UNION
          SELECT swelling_sources.swellingmin, swelling_sources.swellingmax
               , swelling_sources.source
diff --git a/doc/src/functions/build_swelling_states.m4 b/doc/src/functions/build_swelling_states.m4
index 921d0aa..c93491f 100644
--- a/doc/src/functions/build_swelling_states.m4
+++ b/doc/src/functions/build_swelling_states.m4
@@ -126,3 +126,9 @@ Return Value
 
 The function returns the number of rows computed, regardless of
 how many rows previously existed in |SWELLING_STATES|.
+
+.. rubric:: Footnotes
+
+.. [#f1]
+   If there is no such row in |SIGHTING_RECORDS| the corresponding
+   table is not searched for swelling information.
-- 
2.34.1